<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <meta name="generator" content="VuePress 2.0.0-beta.27">
    <meta http-equiv="Content-Security-Policy" content="upgrade-insecure-requests"><script src="https://at.alicdn.com/t/font_2849934_v6y652peian.js"></script><link href="https://gitee.com/ran_yong/mark-down-table-upload/raw/master/img/20211123222242.png" rel="icon"><link rel="stylesheet" type="text/css" href="//at.alicdn.com/t/font_2970797_5eiedcvhf0a.css"><title>MySQL进阶学习 | Sakura</title><meta name="description" content="">
    <link rel="preload" href="/assets/js/runtime~app.5b846f34.js" as="script"><link rel="preload" href="/assets/css/styles.f5148030.css" as="style"><link rel="preload" href="/assets/js/452.9ec739fe.js" as="script"><link rel="preload" href="/assets/js/app.05eaf2e4.js" as="script">
    <link rel="stylesheet" href="/assets/css/styles.f5148030.css">
  </head>
  <body>
    <div id="app"><!--[--><!--[--><div class="common" style="--borderRadius:10px;--opacity:1;--fontColor:#2c3e50;--fontFamily:-apple-system;--fitter-blue:1px;"><header style="--borderRadius:10px;--opacity:1;--fontColor:#2c3e50;--fontFamily:-apple-system;--fitter-blue:1px;" class="header-bg-show navbar" id="c-navbar"><div class="toggle-sidebar-button"><svg class="icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div><span><a href="/" class=""><img style="border-radius:30px;" class="logo" src="https://gitee.com/ran_yong/mark-down-table-upload/raw/master/img/20211123222242.png" alt="Sakura"><span class="site-name" style="color: #2c3e50;">Sakura</span></a></span><div class="navbar-links-wrapper" style=""><!--[--><!--]--><nav class="navbar-links can-hide"><!--[--><div class="navbar-links-item"><!--[--><span class="aurora-nav-font aurora-navbar-icon"></span><a aria-current="page" href="/Back-end/MySQL%E8%BF%9B%E9%98%B6%E5%AD%A6%E4%B9%A0.html#" class="router-link-active router-link-exact-active nav-link nav-link-active" aria-label="面经">面经</a><!--]--></div><div class="navbar-links-item"><div class="dropdown-wrapper"><span class="sakura-html aurora-nav-font aurora-navbar-icon"></span><button class="dropdown-title" type="button" aria-label="前端"><span class="title">前端</span><span class="arrow down"></span></button><button class="mobile-dropdown-title" type="button" aria-label="前端"><span class="title">前端</span><span class="right arrow"></span></button><ul style="display:none;" id="c-nav-dropdown" class="nav-dropdown"><!--[--><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>JavaScript</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Front-end/JavaScript%E8%AF%AD%E6%B3%95%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="JavaScript语言">JavaScript语言</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>TypeScript</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Front-end/TypeScript%E8%AF%AD%E6%B3%95%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="TS语法学习">TS语法学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Vue项目实战</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Front-end/Vue%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98.html" class="nav-link" aria-label="Vue_shop 项目实战">Vue_shop 项目实战</a><!--]--></li><!--]--></ul><!--]--></li><!--]--></ul></div></div><div class="navbar-links-item"><div class="dropdown-wrapper"><span class="sakura-houduankaifa aurora-nav-font aurora-navbar-icon"></span><button class="dropdown-title" type="button" aria-label="后端"><span class="title">后端</span><span class="arrow down"></span></button><button class="mobile-dropdown-title" type="button" aria-label="后端"><span class="title">后端</span><span class="right arrow"></span></button><ul style="display:none;" id="c-nav-dropdown" class="nav-dropdown"><!--[--><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Fastapi</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/Fastapi.html" class="nav-link" aria-label="Fastapi学习">Fastapi学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Python基础</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/python%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="python基础学习">python基础学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Go基础</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/Go%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="Go基础学习">Go基础学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>SQL基础</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/SQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="SQL基础学习">SQL基础学习</a><!--]--></li><!--]--></ul><!--]--></li><!--]--></ul></div></div><div class="navbar-links-item"><div class="dropdown-wrapper"><span class="sakura-service aurora-nav-font aurora-navbar-icon"></span><button class="dropdown-title" type="button" aria-label="服务端"><span class="title">服务端</span><span class="arrow down"></span></button><button class="mobile-dropdown-title" type="button" aria-label="服务端"><span class="title">服务端</span><span class="right arrow"></span></button><ul style="display:none;" id="c-nav-dropdown" class="nav-dropdown"><!--[--><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>宝塔面板</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/WebHook.html" class="nav-link" aria-label="宝塔面板自动同步gitee仓库代码">宝塔面板自动同步gitee仓库代码</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>配置公钥</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/%E9%85%8D%E7%BD%AEgitee%E3%80%81github%E5%85%AC%E9%92%A5.html" class="nav-link" aria-label="配置gitee、github公钥">配置gitee、github公钥</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>宝塔配置HTTPS</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/%E5%AE%9D%E5%A1%94%E9%85%8D%E7%BD%AEHTTPS.html" class="nav-link" aria-label="如何通过宝塔配置HTTPS网站">如何通过宝塔配置HTTPS网站</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Node后台运行</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/%E5%9C%A8linux%E4%B8%AD%E6%8C%81%E7%BB%AD%E8%BF%90%E8%A1%8CNode.js%E6%96%87%E4%BB%B6.html" class="nav-link" aria-label="在linux中持续运行Node.js文件">在linux中持续运行Node.js文件</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Mac安装Scrcpy投屏软件</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/Mac%E5%AE%89%E8%A3%85scrcpy%E6%8A%95%E5%B1%8F%E8%BD%AF%E4%BB%B6.html" class="nav-link" aria-label="Mac M1芯 安装scrcpy投屏软件">Mac M1芯 安装scrcpy投屏软件</a><!--]--></li><!--]--></ul><!--]--></li><!--]--></ul></div></div><div class="navbar-links-item"><!--[--><span class="sakura-shijianzhou aurora-nav-font aurora-navbar-icon"></span><a href="/aurora-archive/" class="nav-link nav-link-active" aria-label="时间轴">时间轴</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-guanyuwomen aurora-nav-font aurora-navbar-icon"></span><a href="/about" class="nav-link nav-link-active" aria-label="关于我">关于我</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-youlianguanli aurora-nav-font aurora-navbar-icon"></span><a href="/link" class="nav-link nav-link-active" aria-label="友链">友链</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-biaoqian aurora-nav-font aurora-navbar-icon"></span><a href="/tag" class="nav-link nav-link-active" aria-label="标签">标签</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-41shuoshuo aurora-nav-font aurora-navbar-icon"></span><a href="/mood" class="nav-link nav-link-active" aria-label="说说">说说</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-xiangce aurora-nav-font aurora-navbar-icon"></span><a href="/photo" class="nav-link nav-link-active" aria-label="相册">相册</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-github aurora-nav-font aurora-navbar-icon"></span><a class="nav-link external nav-link-active" href="https://github.com/ranyong1997/sakura-docs" rel="noopener noreferrer" target="_blank" aria-label="Sakura"><!--[--><!--]--> Sakura<!--[--><!--]--></a><!--]--></div><!--]--></nav><!--[--><!--]--><!----><form class="search-box" role="search"><input type="search" autocomplete="off" spellcheck="false" value><!----></form></div></header><!--[--><div id="" class="mobile-sidebar-control"></div><div id="" class="mobileAnimate mobile-sidebar"><!----></div><!--]--><div class="setColor" style="--borderRadius:10px;--fontFamily:-apple-system;--fontColor:#2c3e50;"><div id="setIco" class="setIco bg_color"></div><div class="welcome-parent" id="welcome-parent"><div class="custom-about m-4 p-4 f4 color-shadow-small bg-gray-800-mktg rounded-2 signup-content-container welcome" style="display: none;" id="welcome"><span class="cancel aurora-iconfont-common aurora-style-cancel" id="cancel"></span><h1 class="common-style" id="sr-only-h2"></h1><div class="custom-top custom-common"><div class="custom-top-span custom-common-span"><span class="aurora-iconfont-common aurora-style-img home-welcome-custom-icon"></span>  </div><div class="custom-top-span custom-common-span"><span class="aurora-iconfont-common aurora-style-blur home-welcome-custom-icon"></span>  </div></div><div class="custom-bottom custom-common"><div style="flex:1;" class="custom-bottom-span custom-common-span"><!--[--><li class="custom-li"><span style="background-color: #2c3e50;" data-color="#2c3e50"></span></li><li class="custom-li"><span style="background-color: #42a5f5;" data-color="#42a5f5"></span></li><li class="custom-li"><span style="background-color: #8093f1;" data-color="#8093f1"></span></li><li class="custom-li"><span style="background-color: #FF6EC7;" data-color="#FF6EC7"></span></li><li class="custom-li"><span style="background-color: #FF7F00;" data-color="#FF7F00"></span></li><li class="custom-li"><span style="background-color: #8FBC8F;" data-color="#8FBC8F"></span></li><li class="custom-li"><span style="background-color: #EAADEA;" data-color="#EAADEA"></span></li><li class="custom-li"><span style="background-color: #3299CC;" data-color="#3299CC"></span></li><!--]--></div></div><div class="custom-bottom custom-common"><div style="flex:1;" class="custom-bottom-span custom-common-span"><!--[--><li class="custom-li"><span style="font-family: -apple-system;" data-color="-apple-system">冉</span></li><li class="custom-li"><span style="font-family: hlt;" data-color="hlt">冉</span></li><li class="custom-li"><span style="font-family: tzt;" data-color="tzt">冉</span></li><li class="custom-li"><span style="font-family: sst;" data-color="sst">冉</span></li><li class="custom-li"><span style="font-family: lf;" data-color="lf">冉</span></li><li class="custom-li"><span style="font-family: xsf;" data-color="xsf">冉</span></li><li class="custom-li"><span style="font-family: lsf;" data-color="lsf">冉</span></li><li class="custom-li"><span style="font-family: cgt;" data-color="cgt">冉</span></li><!--]--></div></div><div class="custom-bottom custom-common"><div class="custom-bottom-span custom-common-span"> 圆角 </div><div style="flex:3;" class="custom-bottom-span custom-common-span"><div class="input-range"><div class="input-left"><input autocomplete min="1" max="40" value="10" step="1" type="range"></div><div class="input-right">10</div></div></div></div><div class="custom-bottom custom-common"><div class="custom-bottom-span custom-common-span"> 透明度 </div><div style="flex:3;" class="custom-bottom-span custom-common-span"><div class="input-range"><div class="input-left"><input autocomplete min="0" max="1" value="1" step="0.01" type="range"></div><div class="input-right">1</div></div></div></div></div></div></div><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><div style="--fontColor: #2c3e50; --fontFamily: -apple-system;" class="sidebar-single-enter-animate theme-container"><div class="page-sidebar"><div class="sidebar-single-enter-animate page-top" id="page-top" show-mood-edit="false"><div class="top-mask" style="background-image: url(https://api.ixiaowai.cn/api/api.php?time=-18403.46153846154);"></div><div class="aurora-top-bubble"><div class="aurora-top-bubble-par"><div class="aurora-top-bubble-box" id="aurora-top-bubble-box"></div><canvas class="aurora-top-bubble-canvas" id="aurora-top-bubble-canvas"></canvas></div></div><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><div class="page-record-control"><div class="page-top-record" id="page-top-record"><div class="page-record-bot-common page-record-top"><div class="page-record-top-left page-record-single-common"><span class="aurora-iconfont-common aurora-page-word"></span>  <span class="page-record-single-desc">总字数</span><span>0</span></div><div class="page-record-top-right page-record-single-common"><span class="aurora-iconfont-common aurora-page-time"></span>  <span class="page-record-single-desc">时长</span><span>0</span></div></div><div class="page-record-bot-common page-record-center"><div class="page-record-center-left page-record-single-common"><span class="aurora-iconfont-common aurora-page-comment"></span>  <span class="page-record-single-desc">评论数</span><span id="/Back-end/MySQL%E8%BF%9B%E9%98%B6%E5%AD%A6%E4%B9%A0.html" class="waline-comment-count"></span></div><div class="page-record-center-right page-record-single-common"><span class="aurora-iconfont-common aurora-page-read"></span>  <span class="page-record-single-desc">总阅读数</span><span id="/Back-end/MySQL%E8%BF%9B%E9%98%B6%E5%AD%A6%E4%B9%A0.html" class="waline-visitor-count"></span></div></div><!----></div></div><div class="top-image" id="top-image"><h1></h1></div></div><div class="content"><div id="article-page-parent" class="article-page-parent-pro article-page-parent"><div class="noShowSidebar page-sidebar-left" id="page-sidebar-left"><!--[--><!--]--><!--[--><!--[--><main style="--borderRadius: 10px;--opacity: 1;" class="page sidebar-single-enter-animate" id="article-page"><!--[--><!--]--><div id="theme-default-content" class="hide-h1-tag theme-default-content pageContent medium-zoom-content"><div class="page-top-share"><div class="page-top-share-next"><div class="poster-share" style="z-index:99;"><div class="poster-button" id="poster-button"><span class="aurora-iconfont-common aurora-poster-ico" data="5">生成海报3</span>  </div></div></div></div><!--[--><h1 id="mysql进阶学习" tabindex="-1"><a class="header-anchor" href="#mysql进阶学习" aria-hidden="true">#</a> MySQL进阶学习</h1><h2 id="表的连接查询方式有哪些-有什么区别" tabindex="-1"><a class="header-anchor" href="#表的连接查询方式有哪些-有什么区别" aria-hidden="true">#</a> 表的连接查询方式有哪些，有什么区别？</h2><ul><li>inner join（内连接） <ul><li>在两张表进行连接查询时，只保留两张表中完全匹配的结果集</li></ul></li><li>left join（左连接） <ul><li>在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录</li></ul></li><li>right join（右链接） <ul><li>在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录</li></ul></li><li>full join（全链接） <ul><li>在两张表进行连接查询时，返回左表和右表中所有没有匹配的行</li></ul></li></ul><h2 id="sql-的-select语句完整的执行顺序" tabindex="-1"><a class="header-anchor" href="#sql-的-select语句完整的执行顺序" aria-hidden="true">#</a> SQL 的 select语句完整的执行顺序</h2><ul><li>from子句组装起来自不同数据源的数据</li><li>where子句基于指定的条件对记录进行筛选</li><li>group by 子句将数据划分为多个组</li><li>使用聚合函数进行计算</li><li>使用having 子句筛选分组</li><li>select 计算所有的表达式</li><li>使用order by 对结果进行排序</li></ul><h2 id="说一下mysql数据库存储的原理" tabindex="-1"><a class="header-anchor" href="#说一下mysql数据库存储的原理" aria-hidden="true">#</a> 说一下Mysql数据库存储的原理?</h2><blockquote><p>存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟</p></blockquote><h2 id="事务的特性" tabindex="-1"><a class="header-anchor" href="#事务的特性" aria-hidden="true">#</a> 事务的特性?</h2><ul><li>原子性</li></ul><blockquote><p>所有操作要么全部成功，要么全部失败回滚</p></blockquote><ul><li>一致性</li></ul><blockquote><p>数据库从一个一致性状态变换到另一个一致性状态，也就是说事务执行之前和执行之后必须都处于一致性状态</p></blockquote><ul><li>隔离性</li></ul><blockquote><p>当多个用户并发访问数据库时，比如操作同一张表，数据库为每一个用户开启的事务，不被其它食物的操作干扰，多个事务之间要相互隔离</p></blockquote><ul><li><p>持久性</p></li><li><blockquote><p>事务一旦提交了，那么对数据库中的数据的改变就是永久性的，即使在数据库系统遇到故障的情况下也不会丢失提交事务的操作</p></blockquote><ul><li><p>脏读</p></li><li><blockquote><p>一个事务处理过程里读取了另一个未提交的事务中的数据</p></blockquote></li><li><p>不可重复读</p></li><li><blockquote><p>对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于查询间隔，被另一个事务修改并提交了</p></blockquote></li><li><p>虚读（幻读）</p></li><li><blockquote><p>事务A按照一定条件进行数据读取，期间事务B插入了相同的搜索条件的新数据，事务A再次按照原先条件进行读取时，发现事务B新插入的数据，称幻读</p></blockquote></li></ul></li></ul><h2 id="数据库索引" tabindex="-1"><a class="header-anchor" href="#数据库索引" aria-hidden="true">#</a> 数据库索引</h2><ul><li>聚集索引（主键索引）：在数据库里面，所有的行树都会按照主键索引进行排序</li><li>非聚集索引：就是给普通字段加上索引</li><li>联合索引：就是好几个字段组成的索引，称为联合索引</li></ul><h2 id="数据库怎么优化查询效率" tabindex="-1"><a class="header-anchor" href="#数据库怎么优化查询效率" aria-hidden="true">#</a> 数据库怎么优化查询效率?</h2><ul><li><p>数据库设计方面</p><ul><li>在一些数字列上设置索引：应避免进行全表扫描，首先应考虑在涉及where或者 order by的列上设置索引</li><li>避免对where子句中数组进行null值判断：否则将导致搜索引擎放弃索引搜索而使用全表搜索</li><li>建立合适的索引</li><li>考虑索引的个数：索引并不是越多越好，索引固然可以提高相应的select的效率，但同时降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑</li><li>尽量使用数字型字段</li></ul></li><li><p>SQL方面</p><ul><li>应尽量避免where子句中使用<code>!=</code>或<code>&lt;&gt;</code>操作符，否则将引擎放弃使用索引而进行全表扫描</li><li>应尽量避免在where子句中使用<code>or</code>来连接条件，否则将导致引擎放弃使用索引而进行全表扫描</li><li>in 和 not in 也要慎用，否则会导致全表扫描</li><li>对于连续的数组，能用 between 就不要用 in 了</li></ul></li><li><p>硬件调整性能</p></li><li><blockquote><p>扩大虚拟内存，并保证有足够的扩充的空间；把数据库服务器上的不必要服务关闭掉；把数据库服务器和主域服务器分开；把SQL数据库服务器的吞吐量调为最大；在具有一个以上处理器的机器上运行SQL</p></blockquote></li><li><p>调整数据库</p></li><li><blockquote><p>若对该表查询频率比较高，则建立索引；建立索引时，想尽对该表的所有查询搜索操作。</p></blockquote></li><li><p>使用存储过程</p></li><li><blockquote><p>应用程序的实现过程中，能够采用存储过程实现的对数据库的操作尽量通过存储过程来实现，因为存储过程时存放在数据库服务器上的一次性被设计、编码、测试，并被再次使用</p></blockquote></li><li><p>应用程序结构算法</p></li><li><blockquote><p>建立插叙你条件索引仅仅是提高速度的前提条件，响应速度的提高还依赖于索引的使用</p></blockquote></li></ul><h2 id="你用的mysql是哪个引擎-各引擎之间有什么区别" tabindex="-1"><a class="header-anchor" href="#你用的mysql是哪个引擎-各引擎之间有什么区别" aria-hidden="true">#</a> 你用的Mysql是哪个引擎，各引擎之间有什么区别?</h2><ul><li>MyISAM引擎 <ul><li>不支持事务，不可以回滚还原</li><li>适合查询以及插入为主的应用</li><li>不支持外键</li><li>清空整个表时，则会重建表</li><li>不支持行锁</li></ul></li><li>InnoDB引擎 <ul><li>支持事务，可以回滚还原</li><li>InnoDB适合频繁修改及涉及到安全性较高的应用</li><li>支持外键</li><li>清空整个表时，一行一行的删除</li><li>支持行锁</li></ul></li></ul><h2 id="提取数据库中倒数10条数据" tabindex="-1"><a class="header-anchor" href="#提取数据库中倒数10条数据" aria-hidden="true">#</a> 提取数据库中倒数10条数据?</h2><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">order</span> <span class="token keyword">by</span> id <span class="token keyword">desc</span> <span class="token keyword">limit</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="请写出truncate和delete-drop表数据的差别" tabindex="-1"><a class="header-anchor" href="#请写出truncate和delete-drop表数据的差别" aria-hidden="true">#</a> 请写出truncate和delete, drop表数据的差别?</h2><ul><li>truncate # 只删除数据</li><li>delete # 只删除数据</li><li>drop # 删除整个表(结构和数据)</li></ul><p>速度上：drop&gt;truncate&gt;delete</p><!--]--></div><footer class="page-meta"><div class="meta-item edit-link"><!--[--><span class="aurora-nav-font aurora-navbar-icon"></span><a class="nav-link external nav-link-active meta-item-label" href="https://github.com/ranyong1997/sakura-docs/edit/main/docs/Back-end/MySQL进阶学习.md" rel="noopener noreferrer" target="_blank" aria-label="编辑"><!--[--><!--]--> 编辑<!--[--><!--]--></a><!--]--></div><!----><!----></footer><!--[--><!--]--></main><main style="--borderRadius:10px;--opacity:1;" class="page box"><div class="theme-default-content"><!--[--><div style="--homePageImgApi:url(https://api.ixiaowai.cn/api/api.php?time=-156640.15384615384);" class="page-bottom-next"><div class="page-next-left page-next-item"><a aria-current="page" href="/Back-end/MySQL%E8%BF%9B%E9%98%B6%E5%AD%A6%E4%B9%A0.html" class="router-link-active router-link-exact-active"><span></span></a></div><!----></div><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--></div></main><div><!----></div><div class="recommend-page"><main style="--borderRadius:10px;--opacity:1;" class="page box"><h2 id="recommend-top-title">推荐阅读</h2><div id="recommend" class="recommend"><!--[--><!--]--></div></main></div><div id="control-comment" class="aurora-control-comment-box"><div style="--borderRadius: 10px;--opacity: 1;" class="theme-comment-box"><span class="aurora-comment-common aurora-iconfont-common page-comment-icon"></span><span class="aurora-comment-common aurora-comment-text">点击评论</span></div><div class="mobile-record aurora-comment-animate"><div class="page box" style="--borderRadius: 10px;--opacity: 1;"><div id="waline" class="vcomment-bottom theme-default-content"></div></div></div></div><!--]--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--></div><div id="page-sidebar-right" style="" class="page-sidebar-right"><div class="stickSidebar"><!--[--><div class="sidebar-cqy"></div><div style="--borderRadius: 10px;--opacity: 1;" id="stickSidebar" class="sidebar-single-enter-animate"><div class="sidebarScroll home-sidebar" style="" id="home-sidebar"><div id="" class="sidebar-single-common"><div class="home-sidebar-avatar"><img id="home-sidebar-avatar-img" src="https://gitee.com/ran_yong/mark-down-table-upload/raw/master/img/20211123222242.png" alt=""></div><div class="home-sidebar-info-desc"><span>Sakura</span></div><div class="home-sidebar-info-page"><div class="sidebar-page-common"><div>文章</div><div>19</div></div><div class="sidebar-page-common"><div>标签</div><div>24</div></div><div class="sidebar-page-common"><div>类别</div><div>4</div></div></div><a target="_blank" href="https://github.com/ranyong1997/sakura-docs"><div class="sidebar-github">Github</div></a><!--[--><!--]--></div><!--[--><!--]--><!----><!--[--><!--]--><div id="" class="sidebar-single-common"><div class="sidebar-link"><!--[--><!--]--></div></div><!----><!--[--><!--]--><!----><!--[--><!--]--><div id="" class="sidebar-single-enter-animate sidebar-single-page sidebar-single-common"><div class="change-page"><div class="catalog-page change-page-common"><span class="changePageActive" index="1">文章目录</span></div><div style="" class="latest-page change-page-common"><!----><span style="" class="" index="2">最新文章</span></div></div><div class="" style=""><!--[--><!--]--></div><div style="display:none;"><!--[--><!--]--></div><!--[--><!--]--></div><!--[--><!--]--><div id="" class="sidebar-single-enter-animate sidebar-single-common"><div class="sidebar-page"><span class="aurora-iconfont-common aurora-sidebar-message"></span><span>公告</span></div><div class="sidebar-message"><!--[--><li id="sidebar-message" class="sidebar-hover-bg-common"><span>这是一个公告<a href="#">Sakura</a></span></li><li id="sidebar-message" class="sidebar-hover-bg-common"><span>祝大家学业进步</span></li><!--]--></div><!--[--><!--]--></div><!--[--><!--]--><div id="" class="sidebar-single-enter-animate sidebar-single-common"><div id="home-tag-sidebar" class="sidebar-page"><span class="aurora-iconfont-common aurora-sidebar-tag"></span><span>标签</span></div><!----><div class="sidebar-tag-item"><!--[--><a href="/tag?tag=Python" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #fdc5f5; font-size: 20px;">Python</span></div></a><a href="/tag?tag=后端" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #00b4d8; font-size: 16px;">后端</span></div></a><a href="/tag?tag=接口" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #a9def9; font-size: 34px;">接口</span></div></a><a href="/tag?tag=Go" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #5fa8d3; font-size: 18px;">Go</span></div></a><a href="/tag?tag=数据库" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #5e548e; font-size: 22px;">数据库</span></div></a><a href="/tag?tag=SQL" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #ded9e2; font-size: 16px;">SQL</span></div></a><a href="/tag?tag=Python基础" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #fbc4ab; font-size: 20px;">Python基础</span></div></a><a href="/tag?tag=前端三剑客" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b5e2fa; font-size: 29px;">前端三剑客</span></div></a><a href="/tag?tag=JavaScript" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #caf0f8; font-size: 15px;">JavaScript</span></div></a><a href="/tag?tag=TypeScript" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #ded9e2; font-size: 27px;">TypeScript</span></div></a><a href="/tag?tag=Vue" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #84dcc6; font-size: 30px;">Vue</span></div></a><a href="/tag?tag=Element UI" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #fbc4ab; font-size: 34px;">Element UI</span></div></a><a href="/tag?tag=Axios" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #62b6cb; font-size: 30px;">Axios</span></div></a><a href="/tag?tag=Scrcpy" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b892ef; font-size: 20px;">Scrcpy</span></div></a><a href="/tag?tag=投屏软件" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #5e548e; font-size: 14px;">投屏软件</span></div></a><a href="/tag?tag=宝塔面板" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #5fa8d3; font-size: 15px;">宝塔面板</span></div></a><a href="/tag?tag=同步代码" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #00b4d8; font-size: 34px;">同步代码</span></div></a><a href="/tag?tag=Node" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #84c7d0; font-size: 28px;">Node</span></div></a><a href="/tag?tag=持续运行" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #fcf6bd; font-size: 18px;">持续运行</span></div></a><a href="/tag?tag=HTTPS" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b39ddb; font-size: 20px;">HTTPS</span></div></a><a href="/tag?tag=SSL" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #0fa3b1; font-size: 32px;">SSL</span></div></a><a href="/tag?tag=Github" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #62b6cb; font-size: 33px;">Github</span></div></a><a href="/tag?tag=Gitee" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #ff9800; font-size: 25px;">Gitee</span></div></a><a href="/tag?tag=公钥" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #62b6cb; font-size: 33px;">公钥</span></div></a><!--]--></div><!--[--><!--]--></div><!--[--><!--]--><!----><!--[--><!--]--></div></div><!--]--></div></div></div></div></div><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><div style="--borderRadius:10px;--opacity:1;--fontColor:#2c3e50;--fontFamily:-apple-system;--fitter-blue:1px;" class="sidebar-single-enter-animate footer-layout-center" data-v-4c868354><div class="footer box" id="footer" data-v-4c868354><!--[--><div class="footer-item" id="footer-item" data-v-4c868354><span>Copyright © by sakura All Rights Reserved.</span></div><div class="footer-item" id="footer-item" data-v-4c868354><span><a target='_blank' href='#' style='display:inline-block;text-decoration:none;height:20px;line-height:20px;'><img src='' style='float:left;'/><p style='float:left;height:20px;line-height:20px;margin: 0px 0px 0px 5px;'>💗</p></a></span></div><!--]--><div class="footer-item" id="footer-item" data-v-4c868354><span data-v-4c868354>小破站已运行207天22小时36分1秒</span></div></div></div></div><div id="set-bg" class="set-bg-fitter" style="--opacity: 1; --borderRadius: 10px; --backgroundImageUrl: url()"></div><div id="posterShade" class=""><span class=""></span></div></div><!----><!--]--><!----><div class="no-aurora-music-box aurora-music-box"><div class="music-box"><div class="no-show-more-song music-more"><!--[--><div class="aurora-music-more-single"><span class="aurora-music-song-active aurora-music-font aurora-music-music1 aurora-music-song-info aurora-music-cursor aurora-music-song">SAKURA</span></div><!--]--></div><div class="music-player"><div class="aurora-music-pic-par"><div class="aurora-music-pic" id="aurora-music-pic"><div class="aurora-music-pause"><span class="aurora-music-bofang4 aurora-music-font aurora-music-control-pause aurora-music-cursor"></span></div><img src="/sakura.png" alt=""></div></div><div class="aurora-music-info"><div class="aurora-music-info-left"></div><div class="aurora-music-info-right"><div class="aurora-music-title aurora-music-info-common"><span class="aurora-music-font aurora-music-music1 aurora-music-song-info aurora-music-cursor aurora-music-song">SAKURA</span></div><div class="aurora-music-bottom aurora-music-info-common"><div class="aurora-music-operate"><div><span class="aurora-music-font aurora-music-shangyishou1 aurora-music-cursor"></span></div></div><div class="aurora-music-operate"><div><span class="aurora-music-font aurora-music-xiayishou2 aurora-music-cursor"></span></div></div><div class="aurora-music-operate"><div><span class="aurora-music-geshunxubofang aurora-music-font aurora-music-cursor"></span></div></div><div class="aurora-music-operate"><div><span class="aurora-music-font aurora-music-gengduotianchong aurora-music-cursor aurora-music-operate-more"></span></div></div></div></div></div></div></div><audio autoplay class="aurora-music-player-control" controls="controls"><source src="musicSrc" type="audio/mp3"> Your browser does not support this audio format. </audio></div><div class="aurora-bubble" style="--aurora-bubble-z-index:-2;" data-v-267f56da><div class="aurora-bubble-box" id="aurora-bubble-box" data-v-267f56da></div><canvas class="aurora-bubble-canvas" id="aurora-bubble-canvas" data-v-267f56da></canvas></div><!--]--></div>
    <script src="/assets/js/runtime~app.5b846f34.js" defer></script><script src="/assets/js/452.9ec739fe.js" defer></script><script src="/assets/js/app.05eaf2e4.js" defer></script>
  </body>
</html>
